Model Table EMPLOYMENT ANALYSIS MEASURES

Attributes

Attribute

Description (where applicable)

Measures (table to be generated as far as possible)

Aggregation of measures is based on SUM if not explicitly noted.

This section describes the basic measures in the cube. These are either measures defined in the core Information Source, in the model itself or in the ETL process. The columns have the following meaning:

• Calculated Y/N: A measure specifically defined for the model, e.g. percentages etc. These measures do not derive from the Information Source.
• Contains Time Intelligence Y/N: Some measures do also have additional time intelligence measures like Year To Date (YTD) Se separate section.
• Definition/Note: Specifies if there is a specific logic behind a measure. More information might also be found in documentation for Information Sources.
• Aggregation Type: Specifies the aggregation type of a measure.
• Hidden Y/N: Specifies if a measure is hidden and there by not visible to the end user when browsing the model.
• Tabular Expression: The DAX expression in the measure definition.

Measure	Calculated Y/N	Contains Time Intelligence Y/N	Definition/Note (where applicable)	Aggregation Type	Hidden Y/N	Tabular Expression
Start Employment Headcount	N	N	Number of employments starting in the period	SUM	N	SUM('EMPLOYEE ANALYSIS'[START_EMPLOYMENT_HEADCOUNT])
Sum Period Current Employments	N	N	Sum of Period Current Employments	SUM	N	SUM('EMPLOYEE ANALYSIS'[Current Employment])
End Employment FTE	N	N	Number of FTE's ending in the period	SUM	N	SUM('EMPLOYEE ANALYSIS'[END_EMPLOYMENT_FTE])
End Employment Headcount	N	N	Number of employments ending in the period	SUM	N	SUM('EMPLOYEE ANALYSIS'[END_EMPLOYMENT_HEADCOUNT])
Start Employment FTE	N	N	Number of FTE's starting in the period	SUM	N	SUM('EMPLOYEE ANALYSIS'[START_EMPLOYMENT_FTE])
Period FTE	N	N	Sum of Period FTE	SUM	N	VAR LastReportingDate = MAX ( 'EMPLOYEE ANALYSIS'[Dim_Headcount_Date_ID] ) RETURN CALCULATE ( SUM ('EMPLOYEE ANALYSIS' [PERIOD_FTE] ), 'REPORTING DATE'[ID] = LastReportingDate )
Avg Period Headcount	N	N	Period headcount divided by date	SUM	N	SUM('EMPLOYEE ANALYSIS'[PERIOD_HEADCOUNT])/DISTINCTCOUNT('EMPLOYEE ANALYSIS'[Dim_Headcount_Date_ID])
Count Employees Rows	N	N	Number of records. Mainly used for calculations	COUNT	N	CALCULATE ( COUNTROWS('EMPLOYEE ANALYSIS'), FILTER ( 'EMPLOYEE ANALYSIS', 'EMPLOYEE ANALYSIS'[Age] <> BLANK() && 'EMPLOYEE ANALYSIS'[SUM_AGE] <> 0 ) )
Age	N	N	Represents current Age of Employee	SUM	N	VAR LastReportingDate = MAX ( 'EMPLOYEE ANALYSIS'[Dim_Headcount_Date_ID] ) RETURN CALCULATE ( SUM ('EMPLOYEE ANALYSIS'[AGE] ), 'REPORTING DATE'[ID] = LastReportingDate )
Current Work Experience	N	N	Number of months	COUNT	N	VAR LastReportingDate = MAX ( 'EMPLOYEE ANALYSIS'[Dim_Headcount_Date_ID] ) RETURN CALCULATE ( SUM ( 'EMPLOYEE ANALYSIS'[CURRENT_WORK_EXP] ), 'REPORTING DATE'[ID] = LastReportingDate )
Total Work Experience	N	N	Number of months	SUM	N	VAR LastReportingDate = MAX ( 'EMPLOYEE ANALYSIS'[Dim_Headcount_Date_ID] ) RETURN CALCULATE ( SUM ('EMPLOYEE ANALYSIS' [TOTAL_WORK_EXP] ), 'REPORTING DATE'[ID] = LastReportingDate )
Period Headcount FTE	N	N	Sum period	SUM	N	VAR LastReportingDate = MAX ( 'EMPLOYEE ANALYSIS'[Dim_Headcount_Date_ID] ) RETURN CALCULATE ( SUM ( 'EMPLOYEE ANALYSIS'[PERIOD_HEADCOUNT] ), 'REPORTING DATE'[ID] = LastReportingDate )
Avg Period FTE	N	N	Period FTE divided by date	SUM	N	SUM('EMPLOYEE ANALYSIS'[PERIOD_FTE])/DISTINCTCOUNT('EMPLOYEE ANALYSIS'[Dim_Headcount_Date_ID])
Avg Period Curr Employment	N	N	Average number of current valid employments	SUM	N	SUM('EMPLOYEE ANALYSIS'[Current Employment])/DISTINCTCOUNT('EMPLOYEE ANALYSIS'[DIM_EMPLOYMENT_ID])
Sum Age	Y	N	Number of months	SUM	N	SUM('EMPLOYEE ANALYSIS'[SUM_AGE])
Start Employment %	Y	N	Start Employment Headcount divided by Period Headcount FTE		N	DIVIDE('EMPLOYMENT ANALYSIS MEASURES'[Start Employment Headcount],'EMPLOYMENT ANALYSIS MEASURES'[Period Headcount FTE])
End Employment %	Y	N	End Employment Headcount divided by Period Headcount FTE		N	DIVIDE('EMPLOYMENT ANALYSIS MEASURES'[End Employment Headcount],'EMPLOYMENT ANALYSIS MEASURES'[Period Headcount FTE])
Avg Age	N	N	Average of SUM_AGE	AVERAGE	N	AVERAGE('EMPLOYEE ANALYSIS'[SUM_AGE])
Avg Total Work Experience	N	N	Total Work Experience divided by Count Employees Rows		N	DIVIDE([Total Work Experience],[Count Employees Rows],0)
Avg Current Work Experience	Y	N	Current Work Experience divided by Period Headcount FTE		N	DIVIDE([Current Work Experience],[Period Headcount FTE],0)
Avg Current Work Experience Yr	Y	N	Avg Current Work Experience per month		N	[Avg Current Work Experience] / 12

USED BY MODEL(S)

Model Name	Product Area
Employee Analysis

Data source Information

The data source for this object is defined using IFS Developer Tool. The data source contains a number of transformations where the model reads data from the last step, i.e. the Data source view name (DW Source View Name).

Additional information can be found in the online documentation of Information Sources.

Model Table	DW Source view (SQL Server)	BI Access View	Referenced Information Source	Recommended Access Type
EMPLOYMENT ANALYSIS MEASURES	FACT_EMPLOYEE_ANALYSIS_TM	FACT_EMPLOYEE_ANALYSIS_BI	FACT_EMPLOYEE_ANALYSIS	On Line